<Query Kind="Expression">
  <Reference>&lt;ProgramFiles&gt;\Microsoft Visual Studio 9.0\Common7\IDE\PublicAssemblies\Microsoft.VisualStudio.QualityTools.UnitTestFramework.dll</Reference>
  <Reference>&lt;RuntimeDirectory&gt;\System.XML.dll</Reference>
</Query>

LINQ to ADO.NET 
This chapter covers the following: 

Mapping LINQ to databases. Classes, properties, and attributes tell LINQ about database tables. 
The DataContext class. This class supports LINQ’s ORM functionality. 
Advanced features. LINQ to SQL supports advanced database features such as transactions, optimist concurrency, stored procedure calls, and more. 
LINQ to SQL in Visual Studio. Support for LINQ to SQL, including IntelliSense and debugging, are added to Visual Studio when LINQ is installed. 
LINQ to DataSet. LINQ to SQL is integrated into ADO.NET, specifically with DataSet objects. 


Introduction 
In Chapter 1 we focused on the standard query operators, looking closely at each method for querying and modifying objects. You now know everything needed to query any data source. Whether data sources are in-memory objects, relational databases, or XML, we use the same uniform syntax to query them. An object is queryable as long as it implements the IQueryable<T> or IEnumerable<T> interface. 

LINQ to SQL implements the IQueryable<T> interface to convert query expressions into Expression trees, which it transforms into SQL statements. 

Results are stored using a basic ORM model, so rows are placed in objects created in our code. The LINQ to SQL run-time infrastructure can track each change to our objects. To persist changes, we call a method, and every tracked change will be propagated to the database. 

LINQ to SQL is compatible with ADO.NET 2.0 classes such as Connection and DataSet. You can easily integrate LINQ to SQL with existing ADO.NET programs; hence this chapter’s title. 
Mapping a Class to a Database Table 
Mapping a class to a database table allows us to use LINQ against the table. The Table attribute, defined in the System.Data.Linq namespace, informs LINQ about how to map a class to a database table. 





--------------------------------------------------------------------------------

Note In this chapter we use a database called People, which has the same structure as the data source in Chapter 1. This allows you to focus on LINQ features and not on database complexities. Instructions for creating the database are included in the downloadable source code from the SourceCode/Download area of www.apress.com. To run the code examples, you must create the People database first. 


--------------------------------------------------------------------------------


The following code simply declares a new public class named Person and associates it with the Person database table. 

[Table(Name="Person")]
public class Person

The Table attribute's Name property is optional. LINQ uses the class name as the default table name. 


Mapping Fields and Properties to Table Columns 
Mapping fields and properties to table columns makes the columns available to LINQ. Figure 2-1 shows the Person table's structure. We want to make all the columns available to LINQ as properties of the Person class. Note that the first column is the primary key. It’s also an IDENTITY column, so SQL Server automatically sets its value. 


Figure 2-1. The Person table's structure




For each column we want to use with LINQ, we need to declare a property and decorate it with the Column attribute. Since we're using properties, we also declare private fields for the underlying data. For the first column, ID, we declare a field, _ID, and a property, ID. 

[Table(Name="Person")]
public class Person
{
   private int _ID;
 
   [Column(Name="ID", Storage="_""ID", 
	  DbType="int NOT NULL IDENTITY",
	  IsPrimaryKey=true, IsDbGenerated=true)]
   public int ID
   {
	  get { return _ID; }
	  set { _ID = value; }
   }
}The Column attribute has 11 properties (see Table 2-1), all of which are optional. We've used five of them. Name specifies the column name. DBType specifies not only the column's data type (int) but also that it's not nullable and is an IDENTITY column. The IsPrimaryKey property indicates that the column is part of the table's primary key. IsDbGenerated indicates that the column's value is generated by the database (which is true for all IDENTITY columns). 

By default, LINQ uses a property's set and get accessors, but we can override this with the Storage property. For example, if we add the Storage property to the Column attribute for ID as follows, LINQ will use the underlying private field, _ID, instead of the accessors. 

[Column(Name="ID", Storage="_ID", 
	  DbType="int NOT NULL IDENTITY",
	  Id=true, IsDbGenerated=true)]
   public int ID
   {
	  get { return _ID; }
	  set { _ID = value; }
   }
}



--------------------------------------------------------------------------------

Note LINQ can persist only class members marked with Column attributes. 


--------------------------------------------------------------------------------


Now declare the private fields and public properties for the rest of the columns. The full class code is in Listing 2-1. 


Listing 2-1. The Person Class Smapped to the Person Table in the People Database 

[Table(Name="Person")]
public class Person
{
   private int _ID;
   private int _IDRole;
   private string _lastName;
   private string _firstName;
 
   [Column(Name="ID", Storage="_ID", 
	   DbType="int NOT NULL IDENTITY",
	   IsPrimaryKey=true, IsDbGenerated=true)]
   public int ID
   {
	  get { return _ID; }
	  set { _ID = value; }
   }
}
 
   [Column(Name="IDRole", Storage="_IDRole", 
	  DbType="int NOT NULL")]
   public int IDRole
   {
	  get { return _IDRole; }
	  set { _IDRole = value; }
   }
 
   [Column(Name="LastName", Storage="_lastName",
	 DbType="nvarchar NOT NULL")]
   public string LastName
   {
	  get { return _lastName; }
	  set { _lastName = value; }
   }
 
   [Column(Name="FirstName", Storage="_firstName",
	 DbType="nvarchar NOT NULL")]
   public string FirstName
   {
	  get { return _firstName; }
	  set { _firstName = value; }
   }
}
Table 2-1. Column-Attribute Properties 

PROPERTY  DESCRIPTION  
AutoSync  Specifies if the column is automatically synchronized from the value generated by the database on insert or update commands. Valid values for this tag are Default, Always, Never, OnInsert, and OnUpdate.  
CanBeNull  A Boolean value that indicates if the column can contain null values (true) or not (false).  
DbType  Specifies the column’s data type in the database. If 
you omit this property, LINQ will infer the type from the class member. This property is mandatory only if you want to use the CreateDatabase method to create a new database instance. 
 
Expression  Defines the column as a computed column. Using this attribute you can define the formula used to compute the result.  
IsDbGenerated  Identifies a column whose value is generated by the database. Usually used in conjunction with primary key columns defined with the IDENTITY property.  
IsDiscriminator  Indicates that the member holds the discriminator value for an inheritance hierarchy.  
IsPrimaryKey  Specifies that a column is part of a table’s primary (or unique) key. LINQ currently works only with tables that have primary (or unique) keys.  
IsVersion  Indicates the member is a database timestamp or version number.  
Name  Specifies the column’s name in the database. Defaults to the member name.  
Storage  Specifies the name of the private field underlying a property. LINQ will bypass the property’s get and set accessors and use the field instead.  
UpdateCheck  Specifies how LINQ detects optimistic concurrency conflicts. The possible values are Always, Never, and WhenChanged. If no member is marked with IsVersion=true, all members participate in detection unless explicitly specified otherwise.  


Creating a Data Context 
A data context is an object of type System.Data.Linq.DataContext. It supports database retrieval and update for objects known to LINQ. It handles the database connection and implements the SQO for database access. To use tables in LINQ, they must not only be mapped but must also be available in a data context. You can make them available in two ways. 

One way is to create a data context and then use it to create an object that LINQ can use as a table. For example, the two lines 

DataContext PeopleDataContext = 
   new DataContext(connString);
Table<Person> People = 
   PeopleDataContext.GetTable<Person>();create a data context, PeopleDataContext, and a Table collection, People (for the Person database table), available in that context. 

DataContext PeopleDataContext = 
   new DataContext(connString);
Table<Person> People = 
   PeopleDataContext.GetTable<Person>();A new generic collection class, Table<T>, in the System.Data.Linq namespace, is used to represent database tables. We used the data context’s GetTable<T> method to create a People object of type Table<Person> in the PeopleDataContext context. The argument to the DataContext constructor is the same thing you provide an ADO.NET connection. Here is an example: 

String connString = @"
   Data Source=.;
   Initial Catalog=People;
   Integrated Security=True
";The result is that our database is known to LINQ as PeopleDataContext and the Person table is known as People. 





--------------------------------------------------------------------------------

Note The Table<T> generic collection type implements IEnumerable<T> and IQueryable<T> as well as ITable, which implements both IEnumerable and IQueryable. 


--------------------------------------------------------------------------------


The other—and recommended—way is to use a strongly-typed data context, like the following: 

public partial class PeopleDataContext : DataContext
{
   public Table<Person> People;
 
   public PeopleDataContext(String connString) : 
	  base(connString) {}
}
In this example we declare a class, PeopleDataContext, to represent the data context. The class has a field, People, for the database table Person. 

The constructor calls the DataContext base constructor with the connection string. 

To use the strongly typed context, we’d instantiate it before performing our first query, like this: 

PeopleDataContext people = 
   new PeopleDataContext(connString);
   In this case our database is known to LINQ as people and the Person table is known as People. 

We've now written all we need for LINQ to manage the Person database table as the People collection. Querying it will be similar to what we did in Chapter 1 to query in-memory objects. 

Querying a Database with LINQ to SQL 
The only difference in querying a database with respect to an in-memory object is that we need to instantiate our data context before our first query. In Listing 2-2, the first line of code in Main() does this. 


Listing 2-2. The Main Class Containing the Code to Query the Database 

class Program
{
   static void Main(string[] args)
   {
	  PeopleDataContext people = 
		 new PeopleDataContext();
 
	  var query =
		 from p in people.People
		 from s in people.Salaries
		 where p.ID == s.ID
		 select new { p.LastName, 
			p.FirstName, 
			s.Year, 
			s.SalaryYear };
 
	  foreach(var row in query)
	  {
		Console.WriteLine(
		   "Name: {0}, {1} - Year: {2}",
		   row.LastName,row.FirstName,row.Year);
		Console.WriteLine("Salary: {0}", 
		   row.SalaryYear);
	  }
   }
}
The DataContext is the two-way channel by which LINQ queries the database and the results are turned into objects. Figure 2-2 shows the output of the code in Listing 2-2. 


Figure 2-2. The output is similar to examples from Chapter1 but this time data is retrieved from a SQL Server database. 





The DataContext class transforms the LINQ query into a SQL query. The Log property of the DataContext class is an easy way to determine the SQL query sent to the database. See the code snippet in Listing 2-3: 


Listing 2-3. Displaying the SQL Query Sent by the Data Context 

PeopleDataContext people = new PeopleDataContext();
 
people.Log = Console.Out;
 
var query =
   from p in people.People
   from s in people.Salaries
   where p.ID == s.ID
   select new { p.LastName, p.FirstName, 
	  s.Year, s.SalaryYear };
 
foreach(var row in query)
{
   Console.WriteLine(
	  "Name: {0}, {1} - Year: {2}",
	  row.LastName, row.FirstName, row.Year);
	  Console.WriteLine("Salary: {0}", 
		 row.SalaryYear);
}The second line redirects the log to the console, as shown in Figure 2-3. 


Figure 2-3. A SQL query sent by a data context 





There is another way to see the SQL sent by LINQ. Use the GetCommand method of DataContext to see SELECT statements. The method requires the LINQ query as an argument and returns an object of DBCommand class. You can use its CommandText property to retrieve the SQL command used by LINQ to SQL to retrieve records from the database. On the other hand, the GetChangeSet method is used for INSERT, UPDATE, and DELETE statements. It returns an object of ChangeSet class that contains Inserts, Updates, and Deletes properties. They give access to a list of objects that is changed after an object associated with a database table is modified. Listing 2-4 shows these two methods in action. 


Listing 2-4. Using GetQueryText and GetChangeText Methods to View SQL Statements 

PeopleDataContext people = new PeopleDataContext();
 
var query =
   from p in people.People
   from s in people.Salaries
   where p.ID == s.ID
   select new { p.LastName, p.FirstName, 
	  s.Year, s.SalaryYear };
Console.WriteLine(
   people.GetCommand(query).CommandText);
Console.WriteLine();
 
foreach(var row in query)
{
   Console.WriteLine(
	  "Name: {0}, {1} - Year: {2}",
	  row.LastName, row.FirstName, row.Year);
   Console.WriteLine("Salary: {0}", 
	  row.SalaryYear);
}
 
Person person = new Person();
person.IDRole = 1;
person.FirstName = "From";
person.LastName = "Code";
 
people.People. InsertOnSubmit (person);
 
Console.WriteLine();
Console.WriteLine
   (people.GetChangeSet().ToString());As you can see from the output shown in Figure 2-4, the ChangeSet object contains an inserted object. Using the Inserts property you can access to the new Person object added to the People collection. 


Figure 2-4. Two other ways to retrieve the SQL statements built by LINQ 








--------------------------------------------------------------------------------

Tip The first LINQ to SQL beta releases had the GetChangeText and GetQueryText methods that returned the SQL statement used to insert, update, or delete the record (the former); and the select command (the latter). Actually they have changed their scope to Internal. You can still call it using Reflection. 

Adding, Modifying, and Deleting Rows 
As you can see in Listing 2-4, adding a new row consists of creating an object of a class that maps to a table, setting its values, and calling the InsertOnSubmit method on the appropriate Table<T> instance. 

This is a classic object-oriented approach—adding an object to a collection—but this time you’re adding a row to a database table! You don’t write any SQL; everything is handled transparently by the data context. However, nothing happens in the database until you call the SubmitChanges method shown in Listing 2-5. 


Listing 2-5. The SubmitChanges Method Propagates Changes to the Database. 

PeopleDataContext people = 
   new PeopleDataContext();
 
Person person = new Person();
person.IDRole = 1;
person.FirstName = "From";
person.LastName = "Code";
people.People.InsertOnSubmit(person);
 
people.SubmitChanges();
 
var query =
   from p in people.People
   select p;
 
foreach(var row in query)
{
   Console.WriteLine(
	  "Name: {0}, {1}", row.LastName, row.FirstName);
}
After the InsertOnSubmit method has modified the Person table with a new row, the SubmitChanges method will contact the database and will execute the related SQL statement. Also, the method will be able to substitute the generic @p0, @p1, and @p2 placeholders with the related value contained in the object. The output in Figure 2-5 shows that a new row has been added into the database. 


Figure 2-5. A new row has been added from the code. 





Let’s you’ll learn to modify and delete rows. To modify a row, we first have to retrieve the row and change the values, as in Listing 2-6. 


Listing 2-6. Modifying a Row with LINQ to SQL 

PeopleDataContext people = new PeopleDataContext();
 
people.Log = Console.Out;
 
var person = people.People.Single(p => p.ID == 5);
 
person.FirstName = "Name";
person.LastName = "Modified";
 
people.SubmitChanges();
In the code snippet, using the Single method we retrieve the unique row whose ID is equal to 5. Then we change some attributes and call SubmitChanges() to update the database table. Figure 2-6 shows the SQL generated by LINQ. 


Figure 2-6. The UPDATE statement built by the LINQ to SQL 





As you can see, LINQ produces an UPDATE statement containing only the columns changed in the code. The first SELECT demonstrates that the Single method searches for the Person with an ID equal to 5. 

Deleting a row is an easier process but it also involves a round trip to the database to first retrieve the row. We can use the Remove method of the DataContext class, and specify the object previously retrieved from a query. Listing 2-7 shows the code. 


Listing 2-7. Using the Remove() Method to Delete a Row from the Database 

PeopleDataContext people = new PeopleDataContext();
 
   people.Log = Console.Out;
 
   // Select the record to remove
   var person = from p in people.People
				where p.ID == 5
				select p;
   people.People.DeleteOnSubmit
	  (person.Single<Person>());
   people.SubmitChanges();
The code retrieves a new Person object using the Single<T> generic method to retrieve only one record. Finally, the object is passed to the DeleteOnSubmit method and the changes are submitted with the SubmitChanges() method call. 

DataContext: Advanced Features 
We've focused our attention on the basic features provided by LINQ to SQL (in the System.Data.Linq.dll assembly). Data contexts have even more features. In the next sections you’ll see how to define relationships between entities, and the benefits of doing that. 

Defining Relationships Between Entities 
The first feature we'll look at regards relationships between tables. A relational database such as Microsoft SQL Server provides the capability to define a relationship between two tables using primary and foreign keys. For example, a table containing a list of orders could have a foreign key pointing to a customers table. Using this relationship we can easily retrieve all the orders made by a specific customer. Moreover, we can define the rules to apply to the rows of related tables when some action occurs. For example, we can inform the database to remove every order row for a customer when the related customer is removed. 

The relationships between objects are defined in a different way. Usually a class contains a collection of related objects from another class. 

LINQ to SQL provides a relational-like way to define a relationship between two entity classes. Thanks to new generic types such as EntitySet<T> and EntityRef<T>, it’s possible to define the class members that are involved in relationships. 

The steps to implement relationships between entity classes are as follows: 

1. Add an EntitySet<T> private field in the parent entity class to collect the objects belonging to the child entity class. 
2. Add the property that encapsulates the access to this private field. Additionally, we have to add the Association decoration to specify some properties, such as the relation name and the keys involved in the relation. 
3. Add the initialization of this private field using its two-parameter constructor. 
4. Add an EntityRef<T> private field in the child entity class to retrieve the instance of the parent entity object. 
5. Add the property that encapsulates the access to this private field. Again, we have to add the Association attribute to the property. 
6. Add the initialization of this private field using the default constructor. 
In the People database, the Person table has a foreign key, IDRole, pointing to the primary key of the Role table. Using the LINQ to SQL Association attribute (in the System.Data.Linq namespace) with the Role and Person class definitions, we can specify this kind of relationship between these tables in our code. Let’s apply these steps to the Role and Person classes. Listing 2-8 gives the code for the parent entity class, Role. 


Listing 2-8. The Role Entity Class 

[Table(Name="Role")]
public class Role
{
   private int _ID;
   private string _Description;
   private EntitySet<Person> _People;
 
   public Role() {
	  _People = new EntitySet<Person>(
		  new Action<Person>(Attach_Person),
		  new Action<Person>(Detach_Person));
   }
 
   [Association(Name="FK_Person_Role",
	  Storage="_People",
	  OtherKey="IDRole")]
   public EntitySet<Person> People
   {
	  get { return _People; }
	  set { _People.Assign(value); }
   }
 
   private void Attach_Person(Person entity) {
	  entity.Role = this;
   }
 
   private void Detach_Person(Person entity) {
	  entity.Role = null;
   }
 
   [Column(Storage = "_ID", Name = "ID",
	  DbType = "Int NOT NULL IDENTITY",
	  IsPrimaryKey = true,
	  IsDbGenerated = true,
	  CanBeNull = false)]
   public int ID
   {
	  get
	  {
		 return this._ID;
	  }
   }
   [Column(Name="RoleDescription",
		   Storage="_Description",
		   DbType="nvarchar NOT NULL",
		   CanBeNull = false)]
   public string RoleDescription
   {
	  get { return _Description; }
	  set { _Description = value; }
   }
}
The Role entity class represents the parent table. That’s why it has 

private EntitySet<Person> _People;which contains the People objects that belong to a role. 

The Role class has to define a public property that encapsulates the access code to the EntitySet<Person> private field. Here is the code snippet for it: 

[Association(Name="FK_Person_Role",
			 Storage="_People",
			 OtherKey="IDRole")]
public EntitySet<Person> People
 
public EntitySet<Person> People
{
   get { return _People; }
   set { _People.Assign(value); }
}The Assign method of EntitySet<T> sets the new value in the collection so that the new object is monitored by LINQ to SQL and by its changetracking service. 

The Association attribute informs LINQ to SQL about the relationship name, the private field used to store the Person objects collection, and the foreign key in the related table. The Association attribute provides the ThisKey property too (see Table 2-2 for the full list of properties). It represents the parent-table key related to the OtherKey. In our example, ThisKey has been omitted because it coincides with the primary key and the LINQ to SQL is able to infer its name automatically. 


Table 2-2. The Full List of the Association Attribute’s Properties 

PROPERTY  DESCRIPTION  
DeleteOnNull  You can decide to delete child objects when their parent is deleted. This is set to true when the Cascade option in the DeleteRule of the relationship defined in SQL Server is specified.  
DeleteRule  Specifies the delete behavior associated with the foreign key. For example, it is possible to add cascade records' deletions when using this property.  
IsForeignKey  When set to true this property indicates that the column represents the foreign key.  
IsUnique  When set to true this property indicates that there is a 1:1 relationship between entities.  
Name  Identifies the name of the relation. Usually its value is the same as the name of the foreign key constraint relation name defined in the database. You have to specify it if you plan to use the CreateDatabase() method from the DataContext class to create a new database with this relation. You have to use the same name in the entity class that composes the relation with this one.  
OtherKey  Identifies a list of parent entity class keys separated by commas. If the keys are not specified, LINQ to SQL infers them, and assumes they are equal to the primary keys defined in the parent entity class.  
Storage  Storage contains the name of the private field defined in the class. When specifying this property, LINQ to SQL will use the class’s field to access data instead of using the related get and set accessors.  
ThisKey  Identifies a list of keys of this entity class, separated by commas. If the keys are not specified, LINQ to SQL assumes they are equal to the primary keys defined in this class.  


The next step is to initialize the private field using the Role class constructor: 

public Role() {
   _People = new EntitySet<Person>(
	  new Action<Person>(Attach_Person),
	  new Action<Person>(Detach_Person));
}We pass two delegate methods to the EntitySet<T> constructor. The Attach_Person method will set the related Role object to the new Person object. The Detach_Person method will set to null the related Role object in the Person object: 

private void Attach_Person(Person entity) {
   entity.Role = this;
}
 
private void Detach_Person(Person entity) {
   entity.Role = null;
}In the child entity class related to the Person database table, we add a private EntityRef<Role> field so we’ll be able to retrieve its role simply: 

Console.WriteLine(person.Role.RoleDescription);Next we have to add a public property containing the accessors to get and set the private field value. In accordance with the steps listed earlier, we have to use the Association attribute even with this public property. Here we should define the same name used in the earlier example because LINQ to SQL has to know that we are going to define the other side of the relation. Moreover, using the ThisKey property we can specify the column name of the child entity class related to the foreign key column of the database table. 

Listing 2-9 shows how to modify the Person class to define the relationship with the Role class: 


Listing 2-9. The Person Class Modified to Include the Relationship with the Role Class 

[Table(Name="Person")]
public class Person
{
   private int _ID;
   private int _IDRole;
   private string _lastName;
   private string _firstName;
 
   public Person() {
	  _Role = default(EntityRef<Role>);
   }
 
   [Column(Name="FirstName",
		   Storage="_firstName",
		   DbType="nvarchar NOT NULL",
		   CanBeNull = false)]
   public string FirstName
   {
	  get { return _firstName; }
	  set { _firstName = value; }
   }
 
   [Column(Name="LastName",
		   Storage="_lastName",
		   DbType="nvarchar NOT NULL",
		   CanBeNull = false)]
   public string LastName
   {
	  get { return _lastName; }
	  set { _lastName = value; }
   }
 
   [Column(Name="IDRole",
		   Storage="_IDRole",
		   DbType="int NOT NULL",
		   CanBeNull = false)]
   public int IDRole
   {
	  get { return _IDRole; }
	  set { _IDRole = value; }
   }
 
   [Column(Storage = "_ID",
   Name = "ID",
   DbType = "Int NOT NULL IDENTITY",
   IsPrimaryKey = true,
   CanBeNull = false)]
	public int ID
	{
	  get
	  {
		 return this._ID;
	  }
   }
   private EntityRef<Role> _Role;
 
   [Association(Name="FK_Person_Role",
				Storage="_Role",
				ThisKey="IDRole",
				OtherKey="ID",
				IsForeignKey=true)]
   public Role Role
   {
	  get { return _Role.Entity; }
	  set {
		 Role previousValue = this._Role.Entity;
		 if (((previousValue != value) || 
			 (this._Role.HasLoadedOrAssignedValue 
			  == false)))
		 {
			if ((previousValue != null))
			{
			   this._Role.Entity = null;
			   previousValue.People.Remove(this);
			}
			this._Role.Entity = value;
			if ((value != null))
			{
			   value.People.Add(this);
			   this._IDRole = value.ID;
			}
			else
			{
			   this._IDRole = default(int);
			}
		 }         }
   }
}
The following class constructor uses the default keyword applied to a generic type to initialize its value: 

public Person() {
   _Role = default(EntityRef<Role>);
}The default keyword applied to a struct data type such as the EntityRef<T> initializes every single field within the struct, setting the reference data types to null and the numeric-value data types to zero. 

In the set accessor we check if the value specified is different from the one within the Entity property of the _Role field or if the _Role field is still not been loaded or assigned. If so, we have to remove the old one before adding the new value. Finally, the Person object pointed by the this keyword is added to the People collection to maintain referential integrity: 

public Role Role
{
   get { return _Role.Entity; }
   set {
	  Role previousValue = this._Role.Entity;
	  if (((previousValue != value) ||
		   (this._Role.HasLoadedOrAssignedValue 
			== false)))
	  {
		 if ((previousValue != null))
		 {
			this._Role.Entity = null;
			previousValue.People.Remove(this);
		 }
		 this._Role.Entity = value;
		 if ((value != null))
		 {
			value.People.Add(this);
			this._IDRole = value.ID;
		 }
		 else
		 {
			this._IDRole = default(int);
		 }
	  } 
   }
}

Using Two Related Entity Classes 
Now that we’ve defined the relationship between two entity classes we can use it to query and modify data. 

The code in Listing 2-10 retrieves a single Person object and then uses its role. 


Listing 2-10. Retrieving a Person and Using Its Role Property 

PeopleDataContext people = 
   new PeopleDataContext();
 
people.Log = Console.Out;
 
var query =
   from p in people.People
   where p.ID == 1
   select p;
 
foreach(var row in query)
{
   Console.WriteLine(
	  "Full Name: {0} {1} Role: {2}",
	  row.FirstName,
	  row.LastName,
	  row.Role.RoleDescription);
}
We don’t need to the join the two tables to access the role. LINQ to SQL generates two SQL queries to retrieve both the Person and related Role data. See the two SQL statements in Figure 2-7. 


Figure 2-7. The output of Listing 2-10 





Note in Figure 2-7 that the @p0 parameter is used in both the queries. They are two different queries, so the @p0 parameter has different values. The @p0 parameter used in the first query is the value specified in the LINQ query. The @p0 parameter used in the second query is the value specified with the ThisKey property of the Association attribute used in the Role property decoration. 

In Listing 2-11 we’ll retrieve a role and then use its People property to add a new person. 


Listing 2-11. Adding a New Person to the Database Starting from a Role 

PeopleDataContext people = new PeopleDataContext();
 
people.Log = Console.Out;
 
Role role = people.Roles.Single(r => r.ID == 1);
 
Person person = new Person();
person.FirstName = "From";
person.LastName = "Relationship";
role.People.Add(person);
 
people.SubmitChanges();
Since there’s a relationship between the two entity classes, we don’t have to specify the IDRole for the Person object. It will be assigned by the Attach_Person delegate function when a new Person object is added to the people collection of the Role entity class. Figure 2-8 shows the INSERT statement generated by LINQ to SQL. The last SELECT statement returns the new Person's ID so that the Person object in the code can be aligned with database-related record. 


Figure 2-8. The INSERT statement generated by LINQ to SQL 





Deleting a row and every row related to it is really simple when a relationship is defined between two entity classes. Listing 2-12 deletes a role and all its related Person records. 


Listing 2-12. Deleting a Role and All of Its Related Person Records 

PeopleDataContext people = new PeopleDataContext();
 
people.Log = Console.Out;
 
Role role = new Role();
role.RoleDescription = "Administrator";
 
Person person = new Person();
person.FirstName = "From";
person.LastName = "Code";
 
role.People.Add(person);
 
people.Roles.InsertOnSubmit(role);
 
people.SubmitChanges();
 
Role admin = people.Roles.Single(r => r.ID == role.ID);
people.Roles.DeleteOnSubmit(admin);
people.SubmitChanges();
It creates a new role as follows 

Role role = new Role();
role.RoleDescription = "Administrator";then adds a new person to it: 

Person person = new Person();
person.FirstName = "From";
person.LastName = "Code";
 
role.People.Add(person);
people.Roles.InsertOnSubmit(role);The code uses the new role’s identifier to retrieve the new row added to the database: 

Role admin = people.Roles.Single(r => r.ID == role.ID);
people.Roles.DeleteOnSubmit(admin);
people.SubmitChanges();We have provided two delegate functions responding to the Add and the Remove events of the Person entity class. In the body of the Detach_Person delegate function we have set the Role value to null, raising a call to the set accessor of the Role property. It’s in the body of Detach_Person that you will find the Remove() method of the Person row related to the role. The Remove() method applied by the parent class will call the Detach delegate function for each child row related to it. This process will be performed once for each Person object related to the Role object. 





--------------------------------------------------------------------------------

Note The code works because there is a Delete Rule defined into the database set to Cascade for the foreign key relationships that relates the Person table to the Role table. On the other hand, you have to remove child records manually from the code before deleting the parent record. 


--------------------------------------------------------------------------------
Other LINQ to SQL Features 
In this section we'll cover the following: 

Using SQLMetal to produce entity classes and associations automatically 
Using the INotifyPropertyChanging interface to communicate with LINQ about changes 
Using the optimistic concurrency and database transactions 
Using stored procedures 
Creating a database from a program 
SQLMetal 
LINQ to SQL has a command-line tool called SQLMetal that generates entity classes, properties, and associations automatically. Table 2-3 lists the SQLMetal options. 


Table 2-3. SQLMetal Generation Tool Options 

OPTION  DESCRIPTION  
/server:<name>  Represents the Microsoft SQL Server server name to which it connects.  
/database:<name>  Represents the Microsoft SQL Server database name to use to produce entity classes.  
/user:<name>  Represents the user’s name to use to connect to the database server.  
/password:<name>  Represents the user’s password to use to connect to the database server.  
/conn:<connectionString>  Lets you specify a connection string to connect to the database.  
/timeout:<value>  Lets you specify the timeout (in seconds) to use for each database command.  
/views  Obtains the database views extraction.  
/functions  Obtains the database user functions extraction.  
/sprocs  Obtains the database stored procedures extraction.  
/dbml:<filename>  Lets you specify a DBML filename that will contain the database metadata and some information about classes and 
properties. 
 
/code:<filename>  Lets you specify the name of the file that will contain the entity classes and data context.  
/map:<filename>  Obtains an external XML file with mapping attributes. The entities produced in the code will not contain class and property attributes' decorations because they have been included in the XML mapping file.  
/language:<name>  There are two options: C# (the default) and VB. Use one of these options to produce a file in the specified language.  
/namespace  Lets you specify the namespace that will contain the generated entity classes.  
/context:<name>  You can specify the name of the class derived by the DataContext class.  
/entitybase:<name>  You can indicate the name of the base entity class from which other entities will inherit.  
/pluralize  Obtains entity class and property names with English plural.  
/serialization:<param>  Generates serializable classes. Possible values are None and Unidirectional.  
/provider:<name>  Lets you specify the name of the provider to use to connect to the database. Possible values are SQLCompact, SQL2000, or SQL2005.  


The following command uses SQLMetal to generate the entity classes to access to the People database within a Microsoft SQL Server 2005 database using Windows Integrated Security: 

sqlmetal /server:pc-ferracchiati /DATABASE:People 
		 /pluralize /code:People.csIf you want to use SQL Server security you have to add two more options to the command, specifying username and password: 

sqlmetal /server:pc-ferracchiati /DATABASE:People /user:sa
		 /password:sapass /pluralize /code:People.csYou can also generate entity classes simply by specifying a database’s data (.MDF) file: 

sqlmetal /pluralize /code:People.cs c:\DATA\people.mdf

The INotifyPropertyChanging Interface 
By opening up the code produced by the SQLMetal tool, we can see some minor differences between it and the code we wrote. There are four types of constructor accepting different connection attributes, such as a connection string and an IDBConnection object, but the big difference is the use of the INotifyPropertyChanging and INotifyPropertyChanged: 

[Table(Name="Person")]
public partial class Person : 
   INotifyPropertyChanging, 
   INotifyPropertyChanged
{
 
   private int _ID;Both the INotifyPropertyChanging interface and the INotifyPropertyChanged interface are in the System.ComponentModel namespace. Both interfaces require two events: 

public event PropertyChangedEventHandler PropertyChanging;
public event PropertyChangedEventHandler PropertyChanged;They also require virtual methods to handle the interfaces: 

protected virtual void SendPropertyChanging() {
   if ((this.PropertyChanging != null)) {
	  this.PropertyChanging(this, 
		 emptyChangingEventArgs);
   }
}
 
protected virtual void SendPropertyChanged
	  (string propertyName) {
   if ((this.PropertyChanged != null)) {
	  this.PropertyChanged
		 (this, new 
			 PropertyChangedEventArgs(propertyName));
   }
}The emptyChangingEventArgs field is a private static class’s field defined in the class as an object of the PropertyChangingEventArgs class created providing an empty string as parameter. In the generated code, each set accessor of a column calls two methods. The SendPropertyChanging method is called just before the variable is set to the provided value. The SendPropertyChanged method is called just after the variable is set. 

[Column(Storage="_ID",
   AutoSync.OnInsert,
   DbType="Int NOT NULL IDENTITY",
   IsPrimaryKey=true,
   IsDbGenerated=true)]
public int ID {
   get {
	  return this._ID;
   }
   set {
	  if ((this._ID != value)) {
			   this.OnIDChanging(value);
			   this.SendPropertyChanging();
			   this._ID = value;
			   this.SendPropertyChanged("ID");
			   this.OnIDChanged();
	  }
   }
}
}



--------------------------------------------------------------------------------

Note Since the column is the IDENTITY type, the SQLMetal adds the AutoSync attribute to refresh column’s value when a new record is inserted. 


--------------------------------------------------------------------------------


The use of INotifyPropertyChanging and INotifyPropertyChanged is not mandatory. In fact, the code we wrote works very well. But these interfaces help LINQ change tracking. The SendPropertyChanging and SendPropertyChanged methods significantly improve change tracking because LINQ doesn’t have to check changes manually. If you don’t use these two interfaces and you don’t inform LINQ about row changes, it will use two copies of the same object to understand if something is changed. There will be two objects representing each table, wasting memory and cycles when you call SubmitChanges(). 

Optimistic Concurrency and Database Transactions 
What we have done to this point works well only if we are the only ones working on a set of data. If an application uses a LINQ query to retrieve data from a table already accessed by another user and then it tries to modify some rows, it could get an exception. This is because LINQ to SQL uses optimistic concurrency. 

LINQ to SQL tracks changes to our objects after they are retrieved by a query and filled by a foreach statement or a call to a caching method such as ToList(). If another user has retrieved a row from the database and already changed its contents, when we try to submit our changes we’ll get an exception. In fact, LINQ's change-tracking service discovers that the row has been changed from its original state (as of when we retrieved it) and raises the exception. To test the optimistic concurrency feature, write and execute the code in Listing 2-13. 


Listing 2-13. Testing the Optimistic Concurrency Feature 

PeopleDataContext people = 
   new PeopleDataContext();
 
Person p = people.People.Single
   (person => person.ID == 1);
 
p.LastName = "Optimistic";
p.FirstName = "Concurrency";
 
Console.ReadLine();
 
people.SubmitChanges();
The code simply retrieves the Person row whose identifier is equal to 1, changes some attributes, and submits the changes after a key is pressed. 

This allows us to execute another instance of the same application that retrieves the same row before we press a key in the other instance of the application. Pressing a key in the first application will modify the row, whereas pressing a key in the second application will cause the exception shown in Figure 2-9. 


Figure 2-9. The exception thrown by LINQ when the optimistic concurrency is violated 





Concurrency is managed by the DataContext class. When we call SubmitChanges(), the data context creates a local transaction using the ReadCommit isolation level; that is, using optimistic concurrency. 

This is the default. When we decorate the properties of the entity classes we can indicate which of them participate in optimistic concurrency. Using the UpdateCheck property of the Column attribute we can specify Never and LINQ will ignore the column during concurrency checking. 

[Column(Name="FirstName",
		Storage="_firstName",
		DbType="nvarchar NOT NULL",
		UpdateCheck=UpdateCheck.Never)]
public string FirstName
{
   get { return _firstName; }
   set { _firstName = value; }
}
 
[Column(Name="LastName",
		Storage="_lastName",
		DbType="nvarchar NOT NULL",
		UpdateCheck=UpdateCheck.Never)]
public string LastName
{
   get { return _lastName; }
   set { _lastName = value; }
}After we modify the Person entity class as shown, the code in Listing 2-13 will work without exceptions because the two columns don’t participate in optimistic concurrency checking. 





--------------------------------------------------------------------------------

Note Before running the example in Listing 2-13 again, you have to change the record to put Anderson Brad as a person in the database. This is necessary because the SELECT executed by the Single method returns the current record that already owns the lastname and firstname values you will change using LINQ. In other words, optimistic concurrency values are already in the database and no UPDATE command will be executed if you specify the same values with LINQ. Obviously, you can change the code and provide different values to LastName and FirstName properties as well. 


--------------------------------------------------------------------------------


LINQ to SQL provides an advanced technique to manage update conflicts. When we call SubmitChanges(), we can specify a ConflictMode enum value to change the way optimistic concurrency is managed by LINQ. 

Using ConflictMode.ContinueOnConflict the ChangeConflictException is filled with some attributes that we can use to personalize the way optimistic concurrency is managed. Using a try statement we can catch the ChangeConflictException and then use the ResolveAll() method provided by the ChangeConflicts property of the DataContext class to specify one of three values from the RefreshMode enumeration that in turn specify three different ways to resolve update conflicts: 

KeepChanges: The old values contained in the object are refreshed with the new values changed by the other client. A new SubmitChanges() call is executed automatically and the current values within the object are used to update the row. 
KeepCurrentValues: This rolls back each change made by the other client to the original database state. A new SubmitChanges() call is executed automatically and the current values within the object are used to update the row. 
OverwriteCurrentValues: The object replaces its data with the new state of the row in the database. 
The code in Listing 2-14 calls the ResolveAll method with KeepChanges after an optimistic concurrency exception has been detected. 


Listing 2-14. A try Statement to Manage Optimistic Concurrency Conflict 

PeopleDataContext people = new PeopleDataContext();
 
Person p = people.People.Single(person => person.ID == 1);
 
p.IDRole = 2;
 
try
{
   people.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException cce)
{
   people.ChangeConflicts.ResolveAll 
	  (RefreshMode.KeepChanges);
}
Sometimes we need to lock a row until we’ve finished managing it. This can be done by using a transaction and the pessimistic concurrency feature. .NET 2.0 provides the TransactionScope class in the System.Transactions namespace. A simple way to implement pessimistic concurrency is with a using statement. Within a using block we can instantiate a TransactionScope and, as the last operation, call its Complete() method (see Listing 2-15). 


Listing 2-15. Implementing Pessimistic Concurrency with TransactionScope 

PeopleDataContext people = new PeopleDataContext();
 
using (TransactionScope t = new TransactionScope())
{
   Person p = people.People.Single(person => person.ID == 1);
 
   p.LastName = "Pessimistic";
   p.FirstName = "Concurrency";
 
   Console.ReadLine();
 
   people.SubmitChanges();
 
   t.Complete();
}
We can test the pessimistic concurrency by executing two separate application instances (like in Listing 2-13). Both transactions attempt to lock the same row, and SQL Server decides which one (the "deadlock victim") to terminate (see Figure 2-10). 


Figure 2-10. Pessimistic concurrency deadlock resolution 





LINQ to SQL is able to integrate itself even with the old ADO.NET application code. We can use the DataContext class with SqlTransaction classes, but we’ll have to do much more work to implement the local transaction. In the code snippet in Listing 2-16 a new Role is added to the related table using an ADO.NET local transaction. 


Listing 2-16. Using an ADO.NET Local Transaction with LINQ to SQL 

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;
 
Role r = new Role();
r.RoleDescription = "Integration with old ADO.NET apps";
people.Roles.InsertOnSubmit(r);
 
people.Connection.Open();
people.Transaction = people.Connection.BeginTransaction();
 
try
{
   people.SubmitChanges();
   people.Transaction.Commit();
}
catch (Exception ex)
{
   people.Transaction.Rollback();
   throw ex;
}
finally
{
   if (people.Connection.State == 
	   System.Data.ConnectionState.Open)
	   people.Connection.Close();
   people.Transaction = null;
}
As you can see in Listing 2-16, we have to pay attention to some things, such as manually opening and closing the connection and calling Commit() or Rollback() (when everything is fine or something goes wrong, respectively). 




Stored Procedures 
LINQ to SQL automatically produces SQL statements to select rows, insert rows, and so on. We often prefer to use existing stored procedures or create new ones to access data and improve application performance. Stored procedures are SQL statement procedures that are precompiled and stored within the SQL Server database. When you call a stored procedure, the database server simply executes it without doing other operations such as checking SQL syntax within it. In many cases calling a stored procedure to retrieve rows works better than using dynamic SQL. 

LINQ to SQL provides the ExecuteCommand method of the DataContext class to call stored procedures. This method has two parameters: the SQL command to execute and the collection of parameters that can be used in the SQL command. 

Within the class inheriting from the DataContext we can add a method to call the ExecuteCommand() method that provides the stored procedure name and its parameters: 

public void InsertRole(Role r)
{
this.ExecuteCommand
   ("exec uspInsertRole @description={0}",
	r.RoleDescription);
}The uspInsertRole stored procedure simply adds a new role, accepting its description as a parameter (Role's identifier is auto-incremented by the server since it is of the identity type). The ExecuteCommand() method will substitute each placeholder specified in the command with the related parameter contained in the collection. 

Up to this point we have written all the necessary code to execute our stored procedure instead of executing the code generated by LINQ to SQL. In Listing 2-17 a new role is added to the related table and the Log property is used to show the code called by LINQ. 


Listing 2-17. A New Role Is Added and the Stored Procedure Is Called Automatically. 

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;
 
Role r = new Role();
r.RoleDescription = "By SP";
people.Roles.InsertOnSubmit(r);
 
people.SubmitChanges();When you executing the code you will obtain the result shown in Figure 2- 11, which displays how LINQ calls the stored procedure automatically. 


Figure 2-11. The framework uses our stored procedure instead of generating the code to insert a new role. 





To update rows using a stored procedure we can specify an update method in the class that inherits from the DataContext class. The prototype of the update method is similar to the one used to insert a record, except using the Update word instead of the Insert word: 

public void UpdateRole(Role newRole)
{
   int iRowsAffected = this.uspUpdateRole(
	  newRole.ID, newRole.RoleDescription);
 
   if (iRowsAffected < 1)
	  throw new ChangeConflictException();
}Moreover, we have to check the return value of uspUpdateRole() because if it is less than 1 an optimistic concurrency error has occurred. In that case we must throw a new ChangeConflictException exception. The uspUpdateRole() method is responsible to call the stored procedure in the People database. Its internal code is similar to other methods you will see soon in a few pages. 





--------------------------------------------------------------------------------

Note In order to retrieve the current number of affected rows after the stored procedure execution you have to add the RETURN @@ROWCOUNTinstruction at the end of the stored procedure code. 


--------------------------------------------------------------------------------


Listing 2-18 shows the code that will call the update stored procedure automatically. 


Listing 2-18. Updating a Role Calling a Stored Procedure Instead of Using the LINQ to SQL Update-Generated Statement 

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;
 
Role r = people.Roles.Single(role => role.ID == 1);
r.RoleDescription = "By Update stored procedure";
 
people.SubmitChanges();
To delete a row by using a stored procedure we have to add a new method in the class that inherits from the DataContext: 

public void DeleteRole(Role r)
{
   this.ExecuteCommand("exec uspDeleteRole @id={0}", r.ID);
}In this way the code in Listing 2-19 will call a stored procedure to remove each role from the database. 


Listing 2-19. Using a Stored Procedure Instead of an Autogenerated Delete Statement to Delete All the Roles That Have a Particular Description 

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;
 
var query = people.Roles
			.Where(role =>
			role.RoleDescription == 
			   "By Update stored procedure ")
			.Select(role => role);
 
foreach (Role r in query)
   people.Roles.DeleteOnSubmit(r);
 
people.SubmitChanges();



--------------------------------------------------------------------------------

Note There is nothing magical about defining a method having a prefixed name and seeing that DataContext calls it automatically. This is a new C# 3.0 feature called partial method. You can use the partial keyword to define a method’s prototype (having particular characteristics such as no return type). This method will be ignored by the compiler if you don’t provide an implementation to the method, leaving just the partial method’s prototype. 


--------------------------------------------------------------------------------


By using SQLMetal with the /sprocs option we can generate entity classes containing methods that have the same name as a stored procedure. Based on the syntax of the stored procedure, the generated code could return a single value or a collection of objects. 

The simplest case is a stored procedure that computes scalar operations using the COUNT operator: 

CREATE procedure uspCountPerson
AS
	declare @count int
	SET @count = (SELECT count(ID) FROM person)
	RETURN @countExecuting the SQLMetal application with the /sprocs option, the generated code will contain the following method: 

[Function(Name="dbo.uspCountPerson")]
public int uspCountPerson() {
   IExecuteResults result =
	  ExecuteMethodCall(this,
		 ((MethodInfo)(MethodInfo.GetCurrentMethod())));
   return ((int)(result.ReturnValue));
}The method will be decorated with the Function attribute where the stored procedure name will be specified. The method name will be similar or equal to the stored procedure name. SQLMetal will infer the method return type by analyzing the SQL statement that the stored procedure uses. For this reason there are some situations SQLMetal tool can't handle. If the stored procedure uses temporary tables or dynamic SQL (by calling the sp_executesql system-stored procedure), the tool will not be able to infer the result's type. Therefore, it will not able to define a related method with a valid return type. These kinds of stored procedures cannot be used with LINQ to SQL. Finally, the body of the generated method contains a call to the ExecuteMethodCall method provided by the DataContext class. This method has two parameters indicating the MethodInfo object for the current method (useful for discovering the stored procedure name by reflection) and a collection of parameters that have to be passed to the stored procedure. Listing 2-20 uses this method to call the related stored procedure. 


Listing 2-20. Using the Method Associated with a Stored Procedure to Retrieve the Number of Person Rows in the Database 

PeopleDataContext people = new PeopleDataContext();
 
Console.WriteLine("Person count = {0}",
				   people.uspCountPerson());
If we have a stored procedure selecting a set of rows, we can use the same technique to produce a method, calling that stored procedure and returning a collection of objects: 

CREATE procedure uspGetRoleDescription
   @description varchar(50)
AS
   SELECT ID, RoleDescription
   FROM Role
   WHERE RoleDescription LIKE @descriptionThe stored procedure in the example returns a set of role rows in which the role description is like a provided parameter. Since the selected columns have been specified in the Role class we can define a method that calls this stored procedure and returns a collection of Role objects. 

[StoredProcedure(Name = "dbo.uspGetRoleDescription")]
public IEnumerable<Role> uspGetRoleDescription(
   [Parameter(Name = "@description")] string description)
{
   IQueryResults<Role> result =
	  ExecuteMethodCall<Role>(this,
		 ((MethodInfo)(MethodInfo.GetCurrentMethod())),
					   description);
	  return ((IEnumerable<Role>)(result));
}When the stored procedure accepts parameters, we have to decorate each related method parameter with the Parameter attribute where we specify its name. The ExecuteMethodCall<T> method uses the properties contained in the class specified as parameter T to fill the object with the column value returned by the stored procedure. Finally, the IQueryResult<T> interface is converted to IEnumerable<T> in order to be used by the iterator reading its records. 





--------------------------------------------------------------------------------

Note SQLMetal is not able to understand if the stored procedure returns values that fit an existing class. It will always generate a new class to contain them. 


--------------------------------------------------------------------------------


Listing 2-21 shows the code that calls this method. 


Listing 2-21. Using the uspGetRoleDescription Method to Retrieve Roles Rows 

PeopleDataContext people = new PeopleDataContext();
 
foreach(Role r in people.uspGetRoleDescription("M%"))
{
   Console.WriteLine("Role: {0} {1}", r.ID.ToString(),
	  r.RoleDescription);
}
The last case supported by LINQ to SQL is for stored procedures using OUTPUT parameters: 

CREATE procedure uspGetTotalSalaryAmountPerYear
	@year int,
	@amount money output
AS
	SET @amount = (SELECT sum(SalaryYear)
				   FROM Salary
				   WHERE year=@year)
	SELECT @amountThe stored procedure above computes the total money amount for the salary in a specified year. When the SQLMetal tool encounters this stored procedure it will produce the following method: 

[Function(Name = "dbo.uspGetTotalSalaryAmountPerYear")]
public ISingleResult<uspGetTotalSalaryAmountPerYearResult>
uspGetTotalSalaryAmountPerYear(
[Parameter(DbType = "Int")] System.Nullable<int> year,
[Parameter(DbType = "Money")]
ref System.Nullable<decimal> amount)
{
   IExecuteResult result = this.ExecuteMethodCall(this,
	  ((MethodInfo)(MethodInfo.GetCurrentMethod())),
					year,
					amount);
   amount = ((System.Nullable<decimal>)
			(result.GetParameterValue(1)));
 
return 
((ISingleResult<uspGetTotalSalaryAmountPerYearResult>)
(result.ReturnValue));
}First the OUTPUT parameter is transformed into a ref method parameter. Then the ISingleResult is used with GetParameterValue() to set the value of the ref variable. The SQLMetal tool generates a new class to contain the retrieved records: the uspGetTotalSalaryAmountPerYear class. 

Listing 2-22 shows the code necessary to execute this method and retrieve the total money amount for the year 2004. 


Listing 2-22. Using the Method Related to the Stored Procedure to Retrieve the Total Money Amount for the Year 2004 

PeopleDataContext people = new PeopleDataContext();
 
decimal? total = 0;
int year = 2004;
 
people.UspGetTotalSalaryAmountPerYear(year, ref total);
 
Console.WriteLine(total.ToString());
User-Defined Functions 
LINQ to SQL also supports user-defined functions (UDFs), which return both scalar values and result sets. 

Using the SQLMetal tool's /functions option, we can obtain a new method in the class that inherits from the DataContext class; the new method is decorated with attributes for building a SQL statement that calls a UDF. 

The following UDF returns the initials of the person whose identifier is specified as an argument: 

CREATE FUNCTION udfGetInitials(@id int)
returns varchar(2)
AS
begin
   declare @initials varchar(2)
   SET @initials = 
	  (SELECT LEFT(FirstName,1) 
		  + LEFT(LastName,1)
	   FROM Person
	   WHERE ID = @id)
 
   RETURN @initials
endExecuting the SQLMetal tool to generate entity classes and user-defined function code, we obtain the following method code: 

[Function(Name = "dbo.udfGetInitials", 
   IsComposable = true)]
public string udfGetInitials
   ([Parameter(DbType = "Int")] Nullable<int> id)
{
   return ((string)(this.ExecuteMethodCall(this,
	  ((MethodInfo)(MethodInfo.GetCurrentMethod())),
	  id).ReturnValue));
}First the Function attribute is used to decorate the method and inform LINQ that it is associated with the UDF specified with the Name parameter. The IsComposable flag set to true indicates to LINQ to SQL that this is a UDF, not a stored procedure. 

Finally, the method's approach is similar to the one seen during stored procedure calling: the ExecuteMethodCall returns an IExecuteResult result. The ReturnValue property is casted to string and returned to the method caller. 

Listing 2-23 shows a code snippet in which the UDF is called within a LINQ query to obtain the initials of each person present in the Person table. 


Listing 2-23. The UDF Is Transformed into a Method That Can be Called as Usual from Our Code. 

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;
 
var query = from p in people.People
 
select new {p.ID, Initials = 
   people.UdfGetInitials(p.ID)};
 
foreach(var row in query)
   Console.WriteLine("PersonID: {0} - Initials: {1}",
					 row.ID, row.Initials);
Figure 2-12 shows the output from Listing 2-23. 


Figure 2-12. The output shows how the UDF calculates the person’s initials. 





As Figure 2-12 shows, the SELECT statement built by LINQ contains an inline call to the UDF. That’s because we have used the related method within our LINQ query. If we use the method outside a query we will obtain a simple statement like this one: 

SELECT dbo.udfGetInitials(@p0)

Database Creation 
Since Microsoft has released a free version of Microsoft SQL Server 2005 called Express Edition, we can easily create an application that stores data using a database instead of XML files or some other data storage. In fact, SQL Server Express Edition can be distributed without limits, allowing us to install and use it even with desktop client applications. Focusing on that feature, a way to create a database on the fly could be really useful. 

LINQ to SQL provides a method of the DataContext class called CreateDatabase. Using the attributes specified in the entity classes, where each column is decorated with options such as column name, column database data type, and so on, LINQ is able to create a new database. 





--------------------------------------------------------------------------------

Note When you need to create a database from scratch using the CreateDatabase method you must use the DbType option for each column. LINQ uses this information to create the column data type. 


--------------------------------------------------------------------------------


Listing in 2-24 shows how you can use CreateDatabase() to create a new database. 


Listing 2-24. Creating a New Database with the CreateDatabase() Method 

PeopleDataContext people = new PeopleDataContext(
@"Data Source=.;Initial Catalog=PeopleFromCode;
   IntegratedSecurity=True");
 
   if (people.DatabaseExists())
	  people.DeleteDatabase();
 
   people.CreateDatabase();
Note the connection string that points to a nonexistent database. The DataContext class uses the connection string to discover whether the database already exists. Otherwise it uses the catalog option specified in the connection string as database name and creates it. Using the DatabaseExists and DeleteDatabase methods we can check if the database already exists and if so, drop it. 

There are some limitations when using the CreateDatabase method to create a database: 

Because stored procedures, UDFs, and triggers are not defined in the entity classes as structure, they are not reproduced. 
Despite the fact that associations could be declared into entity classes, the method is not able to create foreign keys and constraints. 
The application must impersonate a user who has rights to create the database. 

LINQ to SQL in Visual Studio 2008 
Visual Studio 2008 provides functionality to support LINQ application development. The compiler is upgraded to support LINQ query syntax, and IntelliSense supports almost every LINQ component. Further, a really great tool has been added to Visual Studio: the Linq to SQL Classes Designer. It is similar to SQLMetal in that it produces the code to manage entity classes related to database tables, but it has these advantages: 

It produces entity classes just for specified tables, not for the full database. 
It produces entity class associations using a visual tool. 
It customizes the entity class behavior, letting us choose the stored procedures to run when insert/update/delete commands occur. 
It supports entity class hierarchies. 
It is completely a visual tool and the final result offers a visual representation of classes, associations, and so on within a colored diagram. 

A Linq to SQL File Designer Example 
Using Linq to SQL File Designer is very easy. Starting from a Windows Application project, you have to add a new Linq to SQL Classes item to the solution. Visual Studio then shows you the Linq to SQL Classes Designer together with a new toolbox. 

Follow these steps to add database support using Linq to SQL Classes Designer: 

1. Launch Visual Studio 2008 and create a new project with File-->New Project. 
2. Choose the Windows Forms Application template, as shown in Figure 2-13. 

Figure 2-13. Creating a new Windows Forms application from Visual Studio 2008 







3. From the Solution Explorer, right-click on the solution name and choose Add-->New Item from the context menu as shown in Figure 2-14. 

Figure 2-14. Adding a new item to the solution 







4. From the Add New Item dialog box, select the Linq to SQL Classes template and give it a significant name, as shown in Figure 2-15. 

Figure 2-15. Adding a new Linq to SQL File template to the solution 







5. At this point Visual Studio will present the Linq to SQL Classes Designer and provide a new toolbox section (see Figure 2-16). You can use the new toolbox to graphically specify the structure of your table. 

Figure 2-16. The new Linq to SQL Classes toolbox provided by the Linq to SQL Classes Designer 





Double-click on the Class item; a new empty entity class is added to the designer, allowing us to start manipulating it. By right-clicking on the entity class we can add new properties or delete them, as shown in Figure 2-17. 


Figure 2-17. After adding a new Class we can add new properties or delete the class itself 







6. Specify properties in the Properties window (see Figure 2-18). You should be accustomed to the property names because they are the same as column attributes. 

Figure 2-18. The Properties window shows the Property item properties. 







7. Because Linq to SQL Classes Designer supports drag-and-drop from Server Explorer, we are not going to manually create each class from the database. If you can't see Server Explorer, select View-->Server Explorer. From Server Explorer select the Connect to Database button, as shown in Figure 2-19. 

Figure 2-19. Connecting to a database from Server Explorer 







8. From the Add Connection dialog box (shown in Figure 2-20) you specify every parameter—server name, database, and so on—to connect to a database. Select the database server where the People database has been stored. 

Figure 2-20. Add a connection to a database to manage it from the Visual Studio. 







9. Choose a table from Server Explorer and drag it into the Linq to SQL Classes Designer tool. For example, using the People database, drag the Role table 
and drop it into the designer. Visual Studio will present the diagram in Figure 2-21. 


Figure 2-21. The Role table is transformed into the Role entity class after dragging and dropping the table from Server Explorer. 







10. This simple operation has generated a diagram with the dragged table and some code (which you can view by selecting the related ,cs file in Solution Explorer). Now you can drag the Person table from Server 
Explorer and drop it into the Linq to SQL Classes Designer. The final result is shown in Figure 2-22. Because the Role and Person tables have defined a foreign key relation, the Linq to SQL Classes Designer creates an association between the two entity classes automatically. 


Figure 2-22. The association between the Role and Person tables 







11. We want to show two views containing roles and person rows. When we select a row from the Role view the Person view is refreshed to show related person rows. To accomplish this we can add two DataGridView controls to the main form—one called dgRole that will contain role rows, and the other called dgPerson to contain related person rows. 
12. In the Form1 constructor we have to create an object from the PeopleDataContext class generated by the tool to query the database within the source code. 
private PeopleDataContext db;
 
public Form1()
{
   InitializeComponent();
   db = new PeopleDataContext();
}13. Now we have to add the Load event handler in the code, and specify the following code: 
private void Form1_Load(object sender, EventArgs e)
{
   var query = from r in db.Roles
						 select r;
   dgRole.DataSource = query;
   dgPerson.DataSource = dgRole.DataSource;
   dgPerson.DataMember = "Persons";
}The query retrieves all the roles from the database and fills the dgRole data grid. The rest of the code is really interesting because it uses the association between the two entity classes to show only the person-related rows in the data grid. It uses just two simple lines of code! 

14. Press CTRL+F5 to build and execute the code. Selecting the role rows shows that the dgPerson grid displays related rows (see Figure 2-23). 

Figure 2-23. The Windows form application in execution 



Debugging LINQ Applications 
From ScottGu's blog it is possible to download a Visual Studio debugger add-in: the SQL Server Query Visualizer. Follow this thread to install it within your Visual Studio 2008: http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debugvisualizer. aspx. This add-in is really useful because it allows us to check the query syntax built by LINQ before it's sent to the database. Moreover, it allows us to execute the query from the debugger to discover if the result is what we expect. We can also modify the query. 

To use the visualizer we have to put a breakpoint just before the LINQ query definition and press the little magnifying-glass icon that appears when we mouse over the query variable (see Figure 2-24). 


Figure 2-24. Pressing the magnifying-glass icon to use the SQL Server Query Visualizer 





After pressing the magnifying-glass icon the Query Visualizer tool will appear within the debugger (see Figure 2-25). 


Figure 2-25. The SQL Server Query Visualizer in action 





The window is divided in two sections; the upper section is a read-only text area where the LINQ query is displayed after the compiler has transformed it using lambda expressions and methods. The lower section is a writable text area containing the SQL to be executed against the database. The SQL can be executed by clicking the Execute button (see Figure 2-26). 


Figure 2-26. Pressing the Execute button provided by the SQL Server Query Visualizer executes the query, showing the result in the QueryResult window while the application is waiting on a breakpoint. 





Placing the breakpoint just after the query variable definition illustrates that LINQ to SQL uses deferred query execution. Remember that query execution starts only after the query is iterated through a foreach statement or when a caching method such as ToList() is used. The debugger allows us to discover an additional aspect during query execution. Consider the code snippet in Listing 2-25. 


Listing 2-25. Iterating Through Role and Person Rows 

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;
 
var query = from r in people.Roles
			select r;
 
foreach (var role in query)
{
 
   foreach(var person in role.People)
   {
	  Console.WriteLine("Person: {0} {1}", person.FirstName,
		 person.LastName);
   }
}
The code uses the association declared in Role and Person entity classes to iterate through role rows and, with an inner foreach statement, to print the persons that have each role. 

If you put a breakpoint on the query variable within the foreach statement and press F5 to start the debugger, you’ll see that no query has been sent to the database. Press F10 to go a step further; the first query will be printed in the console application (see Figure 2-27). 


Figure 2-27. The first query is sent to the database just after the query variable is iterated. 





Continue pressing F10 to see that a SELECT statement is sent to the database to select person rows each time a new role is processed (see Figure 2-28). 


Figure 2-28. Each time a new role row is processed a new SELECT statement is sent to the database to retrieve related person rows. 





As you can imagine, when you process many rows the database is queried too many times and performance is far from optimal. In such cases, to avoid the deferred loading of rows you can use the LoadWith method provided by the DataLoadOptions class, as shown in Listing 2-26. 


Listing 2-26. Using LoadWith<T>() to Preload the person Rows,Thereby Avoiding the Deferred Loading of Rows 

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;
 
DataLoadOptions shape = new DataLoadOptions();
shape.LoadWith<Role>(r => r.People);
 
people.LoadOptions = shape;
 
var query = from r in people.Roles
			select r;
 
foreach (var role in query)
{
 
   foreach (var person in role.People)
   {
	  Console.WriteLine("Person: {0} {1}",
						 person.FirstName,
						 person.LastName);
   }
}
If you execute the debugger after setting a breakpoint on the query variable you’ll see that just two queries are sent to the database: one to retrieve all the roles and one to retrieve all the people (see Figure 2-29). 


Figure 2-29. When using LoadWith<T>() just two SELECT statements are sent to the database. 





The DataLoadOptions class provides a new method called AssociateWith<> , which is useful to define a subquery against the prefetched records. 




LINQ to DataSet 
In the previous section, you saw how LINQ to SQL supports ADO.NET transactions. This is not the only integration between the "old" ADO.NET library and the "new" LINQ to SQL. In fact, LINQ to SQL can use ADO.NET DataSets with LINQ to DataSet. 

With some limitations, LINQ to DataSet allows developers to use DataSets as normal data sources using the usual LINQ query syntax. 

Listing 2-27 shows a simple example that uses a LINQ query to fill a typed dataset. 


Listing 2-27. Filling a Typed DataTable with the CopyToDataTable Method 

dsPeople ds = new dsPeople();
dsPeople.RoleRow row = 
   ds.Role.NewRoleRow();
row.ID = 1;
row.RoleDescription = "Manager";
ds.Role.AddRoleRow(row);
 
row = ds.Role.NewRoleRow();
row.ID = 2;
row.RoleDescription = "Developer";
ds.Role.AddRoleRow(row);
 
var q = from role in ds.Role
		select role;
 
dsPeople.RoleDataTable t = new dsPeople.RoleDataTable();
q.CopyToDataTable(t, LoadOption.OverwriteChanges);
dsPeople is a typed DataSet added to the Visual Studio project. When you use Visual Studio to create your DataSet objects you can use the DataSet Designer tool, which makes it possible to drag and drop tables from Server Explorer (the same way as when using Linq to SQL Classes Designer). In the dsPeople data set I added the Role table. This operation has automatically created a typed DataSet that contains the Role table together with some other methods and objects. 

The prefilled DataSet object is used in the LINQ query to retrieve all its records. Then the CopyToDataTable method, provided as an extension of the IEnumerable<T> interface, is used to fill a typed DataTable object. This last object will be used in the next code snippet to perform another LINQ query to filter the records. 

The CopyToDataTable method has been added to load a DataTable from a Linq to DataSet query. It provides two versions: the former returns a new DataTable and doesn't accept parameters while the latter fills an existing DataTable provided as parameter, plus a second parameter indicating the LoadOption options (that is, overwrite records). 





--------------------------------------------------------------------------------

Note In existing ADO.NET applications DataSet objects are filled with DataAdapter objects or with other techniques. LINQ to DataSet is completely indifferent about how you fill a DataSet. 


--------------------------------------------------------------------------------


In Listing 2-28 we use the filled dsPeople DataSet just like any data source, and a LINQ query to retrieve a role. 


Listing 2-28. A Typed DataSet Is Queryable Just Like Any Other Data Source. 

var query = from r in t
			where r.ID == 1
			select r;
 
foreach(var row in query)
{
   Console.WriteLine("Role: {0} {1}", row.ID,
	  row.RoleDescription);
}
The Role property contained in the ds DataSet is iterated by using the Rows collection to look for the row whose identifier is equal to 1. 





--------------------------------------------------------------------------------

Tip For our LINQ to DataSet examples you have to leave uncommented the #region regions, both for the code filling the DataSet and for the code querying it. 


--------------------------------------------------------------------------------


LINQ to DataSet adds support for untyped DataSets as well. In this case the code is a bit more complex to write because LINQ has to acquire more information from the query. Listing 2-29 shows how an untyped data set can be filled using a LINQ query. 


Listing 2-29. Filling an Untyped Data Table Using a LINQ Query and the CopyToDataTable Method 

dsPeople ds = new dsPeople();
dsPeople.RoleRow row = ds.Role.NewRoleRow();
row.ID = 1;
row.RoleDescription = "Manager";
ds.Role.AddRoleRow(row);
 
row = ds.Role.NewRoleRow();
row.ID = 2;
row.RoleDescription = "Developer";
ds.Role.AddRoleRow(row);
 
var q = from role in ds.Role
		select role;
 
DataTable dtRole = q.CopyToDataTable();
The CopyToDataTable extended method iterates through the results of the query, creating a new DataTable object filled with DataColumn objects and values. 

Querying an untyped data set is a bit more complex because we have to use the Field<T> class to specify the column's data type; see Listing 2-30. 


Listing 2-30. The Code Using an Untyped Data Set Is More Complex and Less Readable. 

var query = from p in dtRole.AsEnumerable()
			where p.Field<string>("RoleDescription") 
			== "Manager"
			   select p;
 
foreach (var record in query)
{
   Console.WriteLine("Role: {0} {1}",
	 record.Field<int>("ID"),
	 record.Field<string>("RoleDescription"));
}
First of all, the DataTable class doesn’t provide an implementation of the IEnumerable and IQueryable interfaces, so the dtPerson object cannot be used in the LINQ query directly. We have to use the AsEnumerable extended method, which generates an IEnumerable<T> composed of DataTable rows. 

Second, we need to use the 
Field<T>generic method to specify the data 
type of the DataTable column we are going to manage. This is necessary because when using the classic syntax to access a DataTable row (e.g. p["LastName"]) we should cast the return type and also use the IsNull method of the DataRow class to check if the data is null. The Field<T> generic method does all this automatically, plus checks null values when the column accepts nulls (e.g., using Field<string?>). 

As stated previously, LINQ to DataSet has some limitations that should be removed eventually. For example, the CopyToDataTable method doesn’t understand relationships and cannot produce multiple data tables. Moreover, there is no way to use LINQ to DataSet to update database rows after they are retrieved by a LINQ query into a data set object. 




Summary 
In this chapter you saw how to create entity classes and how, thanks to new attributes and their properties, you can easily map those classes to database tables. Then you analyzed DataContext functionality for interfacing with databases. You also saw how defining associations between entity classes simulates relationships between tables. 

You then looked at advanced features, such as optimistic concurrency, stored procedures, and user-defined functions. 

Finally, you used the Visual Studio Linq to SQL Classes Designer tool to create entity classes, and you used its improved debugger. The chapter concluded by analyzing LINQ integration with ADO.NET, specifically with DataSets. 

In the next chapter we’ll use LINQ to manage XML data. 


